"""
Case Type   : 事务-ustore
Case Name   : 事务->子事务->Alter user->kill数据库
Create At   : 2022/1/14
Owner       : @kyleze
Description :
    1.创建用户
    2.在事务中alter用户
    3.kill数据库
    4.查询系统表
    5.环境清理
Expect      :
    1.成功
    2.成功
    3.成功
    4.查询到用户
    5.成功
History     :
"""
import os
import sys
import unittest

from yat.test import macro
from yat.test import Node

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class USTORE(unittest.TestCase):
    def setUp(self):
        self.pri_user = Node('PrimaryDbUser')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.log = Logger()
        self.user1 = 'user_ustore_trans_0007_01'
        self.user2 = 'user_ustore_trans_0007_02'
        text = f'---{os.path.basename(__file__)} start---'
        self.log.info(text)
    
    def testunit_1(self):
        text = '----step1:创建用户;expect:成功----'
        self.log.info(text)
        q_str = f'''create user {self.user1} identified by '{macro.COMMON_PASSWD}';
            create user {self.user2} identified by '{macro.COMMON_PASSWD}';
            '''
        self.log.info(q_str)
        q_res = self.pri_sh.execut_db_sql(q_str)
        self.log.info(q_res)
        self.assertNotIn('ERROR', q_res, '执行失败: ' + text)
        self.assertIn('CREATE ROLE', q_res, '执行失败: ' + text)

        text = '----step2:在事务中alter用户;expect:成功----'
        self.log.info(text)
        q_str = f'''start transaction;
        alter user {self.user1} rename to {self.user1}_01;
        savepoint point0007;
        alter user {self.user2} rename to {self.user2}_02;
        '''
        self.log.info(q_str)
        q_res = self.pri_sh.execut_db_sql(q_str)
        self.log.info(q_res)
        self.assertNotIn('ERROR', q_res, '执行失败: ' + text)
        self.assertIn('ALTER ROLE', q_res, '执行失败: ' + text)

        text = '----step3:kill-重启数据库;expect:成功----'
        self.log.info(text)
        result = self.pri_sh.restart_db_cluster()
        self.log.info(result)
        status = self.pri_sh.get_db_cluster_status()
        self.assertTrue('Degraded' in status or 'Normal' in status)

        text = '----step4:查询用户;expect:r"""(0 rows)----'
        self.log.info(text)
        q_str = f"select usename from pg_user where usename " \
              f"like 'user_ustore_trans_0007%' order by usename;"
        q_res = self.pri_sh.execut_db_sql(q_str)
        self.log.info(q_res)
        self.assertNotIn('ERROR', q_res, '执行失败: ' + text)
        self.assertIn('2 rows', q_res, '执行失败: ' + text)
        self.assertIn(f'{self.user1}', q_res, '执行失败: ' + text)
        self.assertIn(f'{self.user2}', q_res, '执行失败: ' + text)

    def tearDown(self):
        text = '----step5:清理环境;expect:成功----'
        self.log.info(text)
        sql = f'drop owned by {self.user1} cascade;' \
              f'drop owned by {self.user2} cascade;' \
              f'drop user {self.user1};'\
              f'drop user {self.user2};'
        result = self.pri_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn('DROP ROLE', result, '执行失败: ' + text)
        self.assertNotIn('ERROR', result, '执行失败: ' + text)
        text = f'---{os.path.basename(__file__)} end---'
        self.log.info(text)
